-- @owner:宋菲
-- @date:2024-03-15
-- @testpoint:1.测试使用触发器，触发器名称大小写不敏感，合理报错

--关闭参数
set enable_ignore_case_in_dquotes to off;

--创建表及触发器函数;expect:成功
create table "t_ignore_case_in_dquotes_use_case0008_1"(col1 int,col2 varchar(50)); 
create table "t_ignore_case_in_dquotes_use_case0008_2"(col1 int,col2 varchar(50)); 

create or replace function "func_ignore_case_in_dquotes_use_case0008_1"() returns trigger as
$$
declare
begin
    insert into "t_ignore_case_in_dquotes_use_case0008_2" values(1, 'trigger-upperletter');
    return new;
end
$$ language plpgsql;
/

create or replace function "func_ignore_case_in_dquotes_use_case0008_2"() returns trigger as
$$
declare
begin
    insert into "t_ignore_case_in_dquotes_use_case0008_2" values(2, 'trigger-lowerletter');
    return new;
end
$$ language plpgsql;
/

--未开启参数，在表1上创建大写字符的触发器;expect:成功
create trigger "TRI_IGNORE_CASE_IN_DQUOTES_USE_CASE0008_1"
before insert on "t_ignore_case_in_dquotes_use_case0008_1"
for each row
execute procedure "func_ignore_case_in_dquotes_use_case0008_1"();
/

--开启参数
set enable_ignore_case_in_dquotes to on;

--开启参数后，在表1上创建同名小写字符的触发器;expect:成功
create trigger "tri_ignore_case_in_dquotes_use_case0008_1"
before insert on "t_ignore_case_in_dquotes_use_case0008_1"
for each row
execute procedure "func_ignore_case_in_dquotes_use_case0008_2"();
/
--调用触发器;expect:成功
insert into "t_ignore_case_in_dquotes_use_case0008_1" values(1,'one');
--查询表1记录;expect:成功,输出1行记录
select * from t_ignore_case_in_dquotes_use_case0008_1 order by 1;
--查询表2记录;expect:成功,2个触发器都触发，输出2行记录
select * from t_ignore_case_in_dquotes_use_case0008_2 order by 1;

--删除小写名称触发器，清空表数据，并再次插入数据;expect:成功
drop trigger tri_ignore_case_in_dquotes_use_case0008_1 on t_ignore_case_in_dquotes_use_case0008_1;
truncate table t_ignore_case_in_dquotes_use_case0008_1;
truncate table t_ignore_case_in_dquotes_use_case0008_2;
insert into "t_ignore_case_in_dquotes_use_case0008_1" values(1,'one');
--查询表1记录;expect:成功,输出1行记录
select * from t_ignore_case_in_dquotes_use_case0008_1 order by 1;
--查询表2记录;expect:成功,1个触发器触发，输出1行记录
select * from t_ignore_case_in_dquotes_use_case0008_2 order by 1;

--清理环境;expect:成功(4 row)
drop table t_ignore_case_in_dquotes_use_case0008_1;

drop table t_ignore_case_in_dquotes_use_case0008_2;

drop function func_ignore_case_in_dquotes_use_case0008_1;

drop function func_ignore_case_in_dquotes_use_case0008_2;
--关闭参数
set enable_ignore_case_in_dquotes to off;